import mysql.connector
from mysql.connector import Error, pooling
from typing import List, Dict, Optional, Tuple


class DatabaseManager:
    _instance = None  # 单例模式

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super().__new__(cls)
            cls._instance._initialize(*args, **kwargs)
        return cls._instance

    def _initialize(self):
        self.config = {
            'host': 'localhost',
            'user': 'root',
            'password': '12345678',
            'database': 'employee_data',
            'autocommit': False,
            'charset': 'utf8mb4',
            'collation': 'utf8mb4_unicode_ci',
            'connection_timeout': 10
        }
        self.connection = None
        self._connect()

    # def __init__(self):
    #     self.config["pool_size"] = 32
    #     self.config["pool_name"] = "mypool"
    #     self.config["pool_reset_session"] = True
    #     self.connection_pool = pooling.MySQLConnectionPool(**self.config)

    def _connect(self):
        self.connection = self.connection_pool.get_connection()

    def _connect(self):
        try:
            self.connection = mysql.connector.connect(**self.config)
            if not self._is_connected():  # 双重验证
                raise Error("连接验证失败")
            print("成功连接到数据库")
        except Error as e:
            print(f"数据库连接失败: {e}")
            self.connection = None
            raise  # 必须向上抛出异常

    def _reconnect(self):
        retries = 0
        max_retries = 3  # 添加本地计数器
        while retries < max_retries:
            try:
                print(f"正在重连数据库... ({retries + 1}/{max_retries})")
                self._connect()
                if self._is_connected():
                    print("数据库重连成功")
                    return
            except Error as e:
                print(f"重连失败: {e}")
                retries += 1
        raise Error("数据库重连失败")

    def _is_connected(self):
        if self.connection:
            try:
                self.connection.ping(reconnect=True, attempts=3, delay=2)
                return True
            except:
                return False
        return False

    def _keepalive(self):
        """ 保持连接活跃 """
        try:
            if not self._is_connected():
                self._reconnect()
            else:
                with self.connection.cursor() as cursor:
                    cursor.execute("SELECT 1")  # 简单查询保持连接
        except Error as e:
            print(f"连接保持失败: {e}")
            self._reconnect()

    def close(self):
        """ 安全关闭连接 """
        if self.connection:
            try:
                # 连接池的close()方法实际是释放连接
                self.connection.close()
            except Error as e:
                print(f"连接释放警告: {str(e)}")
            finally:
                self.connection = None  # 确保置空

    def validate_connection(func):
        def wrapper(self, *args, **kwargs):
            if not self._is_connected():
                print("检测到无效连接，尝试重建...")
                self._reconnect()  # 移除多余参数
                if not self._is_connected():
                    raise Error("数据库连接不可恢复")
            return func(self, *args, **kwargs)
        return wrapper

    @validate_connection
    def execute_query(self, query: str, params: tuple = None, fetch_all: bool = False) -> Optional[List[Dict]]:
        """
        执行SQL查询并返回结果
        :param query: SQL查询语句
        :param params: 查询参数
        :param fetch_all: 是否获取全部结果
        :return: 查询结果或受影响行数
        """
        if not self.connection:
            raise Error("数据库连接未就绪")

        cursor = None
        try:
            # 获取连接池中的连接（如果使用连接池）
            conn = self.connection_pool.get_connection() if hasattr(self, 'connection_pool') else self.connection

            cursor = conn.cursor(dictionary=True)
            cursor.execute(query, params or ())

            # 处理SELECT查询的多结果集
            if not fetch_all and query.strip().upper().startswith("SELECT"):
                while cursor.nextset():
                    pass

            # 获取查询结果
            if fetch_all:
                result = cursor.fetchall()
                conn.commit()
                return result

            # 返回受影响行数（对于INSERT/UPDATE/DELETE）
            conn.commit()
            return cursor.rowcount

        except Error as e:
            print(f"SQL执行错误: {str(e)}")
            print(f"失败查询: {query}")
            print(f"参数: {params}")

            if 'conn' in locals() and conn.is_connected():
                conn.rollback()
            raise

        finally:
            # 关闭游标并释放连接（如果使用连接池）
            if cursor:
                cursor.close()
            if 'conn' in locals() and hasattr(self, 'connection_pool'):
                conn.close()  # 返回连接到连接池
    # 以下是常用业务方法封装
    def add_employee(self, employee_data: Dict) -> bool:
        """ 添加新职工 """
        query = """
        INSERT INTO employee 
        (employee_id, name, age, gender, education, salary, address, phone)
        VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        params = tuple(employee_data.values())
        return self.execute_query(query, params) == 1

    def get_all_employees(self) -> List[Dict]:
        """ 获取所有职工信息 """
        return self.execute_query("SELECT * FROM employee", fetch_all=True) or []

    def search_employees(self, conditions: Dict) -> List[Dict]:
        """ 组合条件查询 """
        where_clauses = []
        params = []
        for key, value in conditions.items():
            if key == 'salary_range':
                where_clauses.append("salary BETWEEN %s AND %s")
                params.extend(value)
                continue
            if value is not None:
                op = '='
                if key == 'name':
                    op = 'LIKE'
                    value = f"%{value}%"
                where_clauses.append(f"{key} {op} %s")
                params.append(value)

        query = f"SELECT * FROM employee WHERE {' AND '.join(where_clauses)}" if where_clauses else "SELECT * FROM employee"
        return self.execute_query(query, tuple(params), fetch_all=True) or []

    def update_employee(self, employee_id: str, updates: Dict) -> bool:
        """ 更新职工信息 """
        set_clauses = [f"{k} = %s" for k in updates.keys()]
        params = list(updates.values())
        params.append(employee_id)
        query = f"UPDATE employee SET {', '.join(set_clauses)} WHERE employee_id = %s"
        return self.execute_query(query, tuple(params)) == 1

    def delete_employee(self, employee_id: str) -> bool:
        """ 删除职工 """
        query = "DELETE FROM employee WHERE employee_id = %s"
        return self.execute_query(query, (employee_id,)) == 1

    def get_employees_paginated(self, page: int, page_size: int):
        """ 获取分页数据（mysql.connector版） """
        self._keepalive()
        try:
            offset = (page - 1) * page_size
            query = "SELECT * FROM employee LIMIT %s OFFSET %s"
            params = (page_size, offset)
            data = self.execute_query(query, params, fetch_all=True) or []

            total = self.get_employee_count()
            return data, total

        except Error as e:
            print(f"分页查询失败: {e}")
            return [], 0

    def get_employee_count(self) -> int:
        """ 获取总记录数（新增方法） """
        query = "SELECT COUNT(*) AS total FROM employee"
        result = self.execute_query(query, fetch_all=True)
        return result[0]['total'] if result else 0


    def _is_connected(self):
        if not self.connection:
            return False
        try:
            # 使用ping而不是简单属性检查
            status = self.connection.ping(
                reconnect=False,
                attempts=1,
                delay=0
            )
            return status is None  # 成功返回None，失败抛异常
        except Error:
            return False

    def execute_paginated_query(self, page: int, page_size: int,
                                where_clause: str, params: tuple) -> Tuple[List[Dict], int]:
        """
        执行带分页的参数化查询
        :param page: 当前页码
        :param page_size: 每页记录数
        :param where_clause: WHERE子句（不含WHERE关键字）
        :param params: 查询参数
        :return: (当前页数据, 总记录数)
        """
        self._keepalive()
        try:
            # 计算分页参数
            offset = (page - 1) * page_size

            # 构建完整查询
            base_query = "SELECT * FROM employee"
            full_query = f"{base_query} {where_clause}" if where_clause else base_query
            full_query += " LIMIT %s OFFSET %s"

            # 组合参数（原始参数 + 分页参数）
            full_params = params + (page_size, offset)

            # 执行查询
            data = self.execute_query(full_query, full_params, fetch_all=True) or []

            # 获取总记录数
            total = self.get_search_count(where_clause, params)

            return data, total

        except Error as e:
            print(f"分页查询失败: {e}")
            raise

    def get_search_count(self, where_clause: str, params: tuple) -> int:
        """
        获取带条件的查询结果总数
        :param where_clause: WHERE子句（不含WHERE关键字）
        :param params: 查询参数
        :return: 总记录数
        """
        self._keepalive()
        try:
            # 构建计数查询
            base_query = "SELECT COUNT(*) AS total FROM employee"
            full_query = f"{base_query} {where_clause}" if where_clause else base_query

            # 执行查询
            result = self.execute_query(full_query, params, fetch_all=True)
            return result[0]['total'] if result else 0

        except Error as e:
            print(f"计数查询失败: {e}")
            raise


    def get_all_employees(self) -> List[Dict]:
        """ 获取所有职工信息（使用封装好的execute_query方法） """
        try:
            # 使用execute_query执行查询（自动处理连接验证和事务）
            result = self.execute_query(
                query="SELECT * FROM employee",
                fetch_all=True  # 明确要求获取所有结果
            )

            # 返回空列表而不是None（保持与原有逻辑一致）
            return result if result else []

        except Error as e:
            print(f"全量数据获取失败: {str(e)}")
            raise  # 根据业务需求决定是否需要重新抛出异常


